﻿using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using Chevron.OrderDetails.Models;
using Syncfusion.XlsIO;

namespace Chevron.OrderDetails.Exporter.Excel
{
    public static class ExcelWriter
    {
        private const string DECIMAL_FORMAT = @"0,0.##";

        private static void SetTextSummaryHeader(IWorksheet sheet, int row, int col, string text)
        {
            sheet.Range[row, col].Text = text;
            sheet.Range[row, col].CellStyle.Font.Bold = true;
            sheet.Range[row, col].CellStyle.Font.Color = ExcelKnownColors.Indigo;
        }

        private static void PhysicianSummaryReportGenerator(IWorksheet sheet, PhysiciansCatalog catalog,
                                                            LabOrdersList orders)
        {
            int col = 1;
            // Header
            SetTextSummaryHeader(sheet, 1, col++, "UPIN");
            SetTextSummaryHeader(sheet, 1, col++, "Physician");
            SetTextSummaryHeader(sheet, 1, col++, "Pts");
            SetTextSummaryHeader(sheet, 1, col++, "Gross");
            SetTextSummaryHeader(sheet, 1, col++, "Disc");
            SetTextSummaryHeader(sheet, 1, col++, "Net");
            SetTextSummaryHeader(sheet, 1, col++, "Pmt");
            SetTextSummaryHeader(sheet, 1, col++, "Due");
            SetTextSummaryHeader(sheet, 1, col, "Ref");

            // Details
            decimal refTotal = 0;
            decimal grossTotal = 0;
            decimal discTotal = 0;
            decimal netTotal = 0;
            decimal paidTotal = 0;
            decimal dueTotal = 0;
            int patientsTotal = 0;
            int doctorsTotal = 0;

            int row = 2;
            foreach (PhysicianDto doc in catalog.SortByName())
            {
                // Skip the self, unknown physicians
                if ((doc.PhysicianName.Trim() == "Self") || (doc.PhysicianUpin == "<ANON>"))
                    continue;

                col = 1;
                sheet.Range[row, col++].Text = doc.PhysicianUpin;
                sheet.Range[row, col++].Text = doc.PhysicianName;
                sheet.Range[row, col++].Text = doc.NumPatients.ToString(CultureInfo.InvariantCulture);
                sheet.Range[row, col++].Text = doc.GrossBillTotal.ToString(DECIMAL_FORMAT);
                sheet.Range[row, col++].Text = doc.DiscountTotal.ToString(DECIMAL_FORMAT);
                sheet.Range[row, col++].Text = doc.NetBillTotal.ToString(DECIMAL_FORMAT);
                sheet.Range[row, col++].Text = doc.PaymentTotal.ToString(DECIMAL_FORMAT);
                sheet.Range[row, col++].Text = doc.DueTotal.ToString(DECIMAL_FORMAT);
                sheet.Range[row++, col].Text = doc.NetReferralTotal.ToString(DECIMAL_FORMAT);

                refTotal += doc.NetReferralTotal;
                grossTotal += doc.GrossBillTotal;
                discTotal += doc.DiscountTotal;
                netTotal += doc.NetBillTotal;
                paidTotal += doc.PaymentTotal;
                dueTotal += doc.DueTotal;
                patientsTotal += doc.NumPatients;
                doctorsTotal++;
            }

            // Footer
            col = 1;
            SetTextSummaryHeader(sheet, row, col++, "[SUMMARY]");
            SetTextSummaryHeader(sheet, row, col++, doctorsTotal.ToString(CultureInfo.InvariantCulture));
            SetTextSummaryHeader(sheet, row, col++, patientsTotal.ToString(CultureInfo.InvariantCulture));
            SetTextSummaryHeader(sheet, row, col++, grossTotal.ToString(DECIMAL_FORMAT));
            SetTextSummaryHeader(sheet, row, col++, discTotal.ToString(DECIMAL_FORMAT));
            SetTextSummaryHeader(sheet, row, col++, netTotal.ToString(DECIMAL_FORMAT));
            SetTextSummaryHeader(sheet, row, col++, paidTotal.ToString(DECIMAL_FORMAT));
            SetTextSummaryHeader(sheet, row, col++, dueTotal.ToString(DECIMAL_FORMAT));
            SetTextSummaryHeader(sheet, row, col, refTotal.ToString(DECIMAL_FORMAT));
        }

        private static void SetTextSuperDetailed(IWorksheet sheet, int row, int col, string text)
        {
            sheet[row, col].Text = text;
            sheet[row, col].CellStyle.Font.Italic = true;
        }

        private static int PhysicianSuperDetailedReport(IWorksheet sheet, OrderDto order, int row)
        {
            foreach (OrderDetailDto test in order.Tests)
            {
                if (test.IsCancelled)
                {
                    continue;
                }

                if (test.LabTest.Category.ReferalType != ReferalType.None)
                {
                    int col = 4;
                    SetTextSuperDetailed(sheet, row, col++, test.LabTest.Category.Name);
                    SetTextSuperDetailed(sheet, row, col++, test.LabTest.Price.ToString(DECIMAL_FORMAT));
                    SetTextSuperDetailed(sheet, row, col++,
                                         (test.LabTest.Category.ReferalType == ReferalType.Percent) ? "%" : "F");
                    SetTextSuperDetailed(sheet, row++, col, test.Referral.ToString(DECIMAL_FORMAT));
                }
            }
            return row;
        }

        private static int PhysicianDetailedReport(IWorksheet sheet, PhysicianDto doctor, LabOrdersList orders, int row,
                                                   bool superDetails)
        {
            IOrderedEnumerable<OrderDto> thisOrders = from o in orders
                                                      where o.Physician.PhysicianUpin == doctor.PhysicianUpin
                                                      orderby o.OrderDateTime
                                                      select o;

            foreach (OrderDto order in thisOrders)
            {
                int col = 2;
                sheet[row, col++].Text = order.OrderDateTime.ToString("dd/MM/yy");
                sheet[row, col++].Text = order.OrderNum;
                sheet[row, col++].Text = order.InvoiceNum.ToString(CultureInfo.InvariantCulture);
                sheet[row, col++].Text = order.GrossBill.ToString(DECIMAL_FORMAT);
                sheet[row, col++].Text = order.Discount.ToString(DECIMAL_FORMAT);
                sheet[row, col++].Text = order.NetBill.ToString(DECIMAL_FORMAT);
                sheet[row, col++].Text = order.Payment.ToString(DECIMAL_FORMAT);
                sheet[row, col++].Text = order.Due.ToString(DECIMAL_FORMAT);
                sheet[row++, col].Text = order.NetReferral.ToString(DECIMAL_FORMAT);

                if (superDetails)
                {
                    row = PhysicianSuperDetailedReport(sheet, order, row);
                }
            }

            return row;
        }

        private static void PhysicianDetailedReportGenerator(IWorksheet sheet, PhysiciansCatalog catalog,
                                                             LabOrdersList orders)
        {
            DetailedReportWrapper(sheet, catalog, orders, false);
        }

        private static void PhysicianSuperDetailedReportGenerator(IWorksheet sheet, PhysiciansCatalog catalog,
                                                                  LabOrdersList orders)
        {
            DetailedReportWrapper(sheet, catalog, orders, true);
        }

        private static void DetailedReportWrapper(IWorksheet sheet, PhysiciansCatalog catalog, LabOrdersList orders,
                                                  bool superDetails)
        {
            int row = 1;
            foreach (PhysicianDto doc in catalog.SortByName())
            {
                // Skip the self, unknown physicians
                if ((doc.PhysicianName.Trim() == "Self") || (doc.PhysicianUpin == "<ANON>"))
                    continue;

                sheet.Range[row, 1].Text = doc.PhysicianUpin;
                sheet.Range[row, 1].CellStyle.Font.Bold = true;
                sheet.Range[row, 1].CellStyle.Font.Color = ExcelKnownColors.Indigo;

                sheet.Range[row, 4].Text = doc.PhysicianName;
                sheet.Range[row, 4].CellStyle.Font.Bold = true;
                sheet.Range[row++, 4].CellStyle.Font.Color = ExcelKnownColors.Indigo;

                row = PhysicianDetailedReport(sheet, doc, orders, row, superDetails);
            }
        }

        public static void ExportPhysicianSummaryReport(PhysiciansCatalog catalog, LabOrdersList orders, string filename)
        {
            CreateExcelReport(catalog, orders, filename, PhysicianSummaryReportGenerator);
        }

        public static void ExportPhysicianDetailedReport(PhysiciansCatalog catalog, LabOrdersList orders,
                                                         string filename)
        {
            CreateExcelReport(catalog, orders, filename, PhysicianDetailedReportGenerator);
        }

        public static void ExportPhysicianSuperDetailedReports(PhysiciansCatalog catalog, LabOrdersList orders,
                                                               string folder)
        {
            foreach (PhysicianDto doc in catalog.SortByName())
            {
                if ((doc.PhysicianName.Trim() == "Self") || (doc.PhysicianUpin == "<ANON>"))
                    continue;

                var filename = Path.Combine(folder, Path.ChangeExtension(doc.PhysicianUpin, "xls"));
                using (var excel = new ExcelEngine())
                {
                    IApplication app = excel.Excel;
                    IWorkbook workbook = app.Workbooks.Create();
                    workbook.Version = ExcelVersion.Excel97to2003;
                    IWorksheet sheet = workbook.Worksheets[0];

                    var ordersFiltered = from o in orders
                                         where o.Physician.PhysicianUpin == doc.PhysicianUpin
                                         orderby o.OrderDateTime
                                         select o;
                    DetailedReportSinglePhysician(sheet, ordersFiltered, doc);

                    workbook.SaveAs(filename);
                }
            }
        }

        private static void DetailedReportSinglePhysician(IWorksheet sheet, IEnumerable<OrderDto> orders,
                                                          PhysicianDto doc)
        {
            int row = 1;
            sheet.Range[row, 1].Text = "UPIN:";
            
            sheet.Range[row, 2].Text = doc.PhysicianUpin;
            sheet.Range[row, 2].CellStyle.Font.Bold = true;
            sheet.Range[row, 2].CellStyle.Font.Color = ExcelKnownColors.Indigo;

            sheet.Range[row, 4].Text = "Name:";
            
            sheet.Range[row, 5].Text = doc.PhysicianName;
            sheet.Range[row, 5].CellStyle.Font.Bold = true;
            sheet.Range[row++, 5].CellStyle.Font.Color = ExcelKnownColors.Indigo;

            int col = 2;
            sheet[row, col].Text = "Date";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "ID";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Inv";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Gross";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Disc.";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Net";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Paid";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Due";
            sheet.Range[row, col++].CellStyle.Font.Bold = true;
            sheet[row, col].Text = "Ref.";
            sheet.Range[row++, col].CellStyle.Font.Bold = true;

            foreach (var orderDto in orders)
            {
                row = PhysicianSuperDetailedReportSingle(sheet, orderDto, row);
            }

            sheet[++row, 1].Text = "Total Referral:";
            sheet[row, 1].CellStyle.Font.Bold = true;
            sheet[row, 3].Text = "Tk. " + doc.NetReferralTotal.ToString(DECIMAL_FORMAT);
            sheet[row, 3].CellStyle.Font.Bold = true;
        }

        private static int PhysicianSuperDetailedReportSingle(IWorksheet sheet, OrderDto order, int row)
        {
            int col = 2;
            sheet[row, col++].Text = order.OrderDateTime.ToString("dd/MM/yy");
            sheet[row, col++].Text = order.OrderNum;
            sheet[row, col++].Text = order.InvoiceNum.ToString(CultureInfo.InvariantCulture);
            sheet[row, col++].Text = order.GrossBill.ToString(DECIMAL_FORMAT);
            sheet[row, col++].Text = order.Discount.ToString(DECIMAL_FORMAT);
            sheet[row, col++].Text = order.NetBill.ToString(DECIMAL_FORMAT);
            sheet[row, col++].Text = order.Payment.ToString(DECIMAL_FORMAT);
            sheet[row, col++].Text = order.Due.ToString(DECIMAL_FORMAT);
            sheet[row, col].Text = order.NetReferral.ToString(DECIMAL_FORMAT);
            sheet[row++, col].CellStyle.Font.Bold = true;

            return PhysicianSuperDetailedReport(sheet, order, row);
        }


        public static void ExportPhysicianSuperDetailedReport(PhysiciansCatalog catalog, LabOrdersList orders,
                                                              string filename)
        {
            CreateExcelReport(catalog, orders, filename, PhysicianSuperDetailedReportGenerator);
        }

        private static void CreateExcelReport(PhysiciansCatalog catalog, LabOrdersList orders, string filename,
                                              PhysicianReportGenerator generator)
        {
            using (var excel = new ExcelEngine())
            {
                IApplication app = excel.Excel;
                IWorkbook workbook = app.Workbooks.Create();
                workbook.Version = ExcelVersion.Excel97to2003;
                IWorksheet sheet = workbook.Worksheets[0];

                generator(sheet, catalog, orders);

                workbook.SaveAs(filename);
            }
        }

        #region Nested type: PhysicianReportGenerator

        private delegate void PhysicianReportGenerator(IWorksheet sheet, PhysiciansCatalog catalog, LabOrdersList orders);

        #endregion
    }
}